﻿#include "DbMgr.h"
#include <QSqlQuery>
#include <QSqlError>
#include <QDebug>
#include <QMetaObject>
#include <QSqlRecord>
#include <QUuid>
#include <QApplication>

DbMgr::DbMgr() : QObject(nullptr) {
	// 创建并启动数据库线程
	m_dbThread = new QThread();
	this->moveToThread(m_dbThread);
	m_dbThread->start();

	// 连接信号槽（通用处理）- 显式指定为队列连接
	connect(this, &DbMgr::sigNonTransOp, this, &DbMgr::onNonTransOp, Qt::QueuedConnection);
	connect(this, &DbMgr::sigBeginTrans, this, &DbMgr::onBeginTrans, Qt::QueuedConnection);
	connect(this, &DbMgr::sigTransOp, this, &DbMgr::onTransOp, Qt::QueuedConnection);
	connect(this, &DbMgr::sigCommitTrans, this, &DbMgr::onCommitTrans, Qt::QueuedConnection);
	connect(this, &DbMgr::sigRollbackTrans, this, &DbMgr::onRollbackTrans, Qt::QueuedConnection);

	// 连接结果回调信号（触发后执行用户传入的回调并自动清理）
	connect(this, &DbMgr::sigResult, qApp, [this](const QUuid& callbackId, const DbResult& result) {
		auto it = m_callbacks.find(callbackId);
		if (it != m_callbacks.end()) {
			it.value()(result);  // 执行用户回调
			m_callbacks.erase(it);  // 清理回调
		}
		}, Qt::QueuedConnection);  // ← 这里也要加

	connect(this, &DbMgr::sigTransBeginResult, qApp, [this](const QUuid& callbackId, bool success, const QString& error, const QUuid& transId) {
		auto it = m_beginTransCallbacks.find(callbackId);
		if (it != m_beginTransCallbacks.end()) {
			it.value()(success, error, transId);  // 执行用户回调
			m_beginTransCallbacks.erase(it);  // 清理回调
		}
		}, Qt::QueuedConnection);  // ← 这里也要加

	connect(this, &DbMgr::sigTransCommitRollbackResult, qApp, [this](const QUuid& callbackId, bool success, const QString& error) {
		auto it = m_commitRollbackCallbacks.find(callbackId);
		if (it != m_commitRollbackCallbacks.end()) {
			it.value()(success, error);  // 执行用户回调
			m_commitRollbackCallbacks.erase(it);  // 清理回调
		}
		}, Qt::QueuedConnection);  // ← 这里也要加
}

void DbMgr::configDb()
{
	//提高性能的PRAGMA设置
	if (m_driver == "QSQLITE") {
		QSqlQuery query(m_database);
		query.exec("PRAGMA journal_mode = WAL;"); // 启用写时复制模式
		query.exec("PRAGMA synchronous = NORMAL;"); // 设置同步级别为NORMAL
		query.exec("PRAGMA temp_store = MEMORY;"); // 临时表存储在内存中
		query.exec("PRAGMA cache_size = 10000;"); // 设置缓存大小（单位为页）
		query.exec("PRAGMA locking_mode = EXCLUSIVE;"); // 独占锁定模式
		qDebug() << "SQLite数据库性能优化设置已应用";
	}
}

DbMgr::~DbMgr() {
	// 停止线程
	if (m_dbThread) {
		m_dbThread->quit();
		m_dbThread->wait();
		delete m_dbThread;
	}

	// 关闭数据库
	if (m_database.isOpen()) {
		m_database.close();
	}
	if (QSqlDatabase::contains(m_database.connectionName())) {
		QSqlDatabase::removeDatabase(m_database.connectionName());
	}
}

bool DbMgr::init(const QString& dbName, const QString& driver) {
	m_dbName = dbName;
	m_driver = driver;

	bool initSuccess = false;
	// 在DB线程中初始化连接（阻塞等待结果）
	QMetaObject::invokeMethod(this, [this, &initSuccess]() {
		if (m_database.isOpen()) m_database.close();

		// 生成唯一连接名（避免冲突）
		QString connName = "DbConn_" + QString::number((quint64)this, 16);
		if (QSqlDatabase::contains(connName)) {
			QSqlDatabase::removeDatabase(connName);
		}

		m_database = QSqlDatabase::addDatabase(m_driver, connName);
		m_database.setDatabaseName(m_dbName);

		configDb();

		if (!m_database.open()) {
			m_lastError = m_database.lastError().text();
			qWarning() << "数据库打开失败:" << m_lastError;
			initSuccess = false;
		}
		else {
			initSuccess = true;
		}
		}, Qt::BlockingQueuedConnection);

	return initSuccess;
}

bool DbMgr::checkConnection() {
	if (!m_database.isValid()) {
		m_lastError = "数据库连接无效";
		return false;
	}
	if (!m_database.isOpen() && !m_database.open()) {
		m_lastError = m_database.lastError().text();
		return false;
	}
	return true;
}

bool DbMgr::isTransactionActive(const QUuid& transId) {
	return m_transactions.contains(transId) && m_transactions[transId];
}

// -------------------------- 非事务操作实现 --------------------------
void DbMgr::select(const QString& table, const QString& fields, const QString& condition, const QString& join, std::function<void(const DbResult&)> callback)
{
	QMetaObject::invokeMethod(this, [this, table, fields, condition, join, callback]() {
		QUuid callbackId = QUuid::createUuid();
		if (callback) {
			m_callbacks[callbackId] = callback;
		}
		QVariantMap params;
		params["table"] = table;
		params["fields"] = fields;
		params["condition"] = condition;
		params["join"] = join;
		onNonTransOp("select", params, callbackId);
		}, Qt::QueuedConnection);
}

void DbMgr::insert(const QString& table, const QVariantMap& values, std::function<void(const DbResult&)> callback)
{
	QMetaObject::invokeMethod(this, [this, table, values, callback]() {
		QUuid callbackId = QUuid::createUuid();
		if (callback) {
			m_callbacks[callbackId] = callback;
		}
		QVariantMap params;
		params["table"] = table;
		params["values"] = values;
		onNonTransOp("insert", params, callbackId);
		}, Qt::QueuedConnection);
}

void DbMgr::update(const QString& table, const QVariantMap& values, const QString& condition, std::function<void(const DbResult&)> callback)
{
	QMetaObject::invokeMethod(this, [this, table, values, condition, callback]() {
		QUuid callbackId = QUuid::createUuid();
		if (callback) {
			m_callbacks[callbackId] = callback;
		}
		QVariantMap params;
		params["table"] = table;
		params["values"] = values;
		params["condition"] = condition;
		onNonTransOp("update", params, callbackId);
		}, Qt::QueuedConnection);
}

void DbMgr::executeSql(const QString& sql, std::function<void(const DbResult&)> callback)
{
	QMetaObject::invokeMethod(this, [this, sql, callback]() {
		QUuid callbackId = QUuid::createUuid();
		if (callback) {
			m_callbacks[callbackId] = callback;
		}
		QVariantMap params;
		params["sql"] = sql;
		onNonTransOp("executeSql", params, callbackId);
		}, Qt::QueuedConnection);
}

void DbMgr::deleteRecord(const QString& table, const QString& condition, std::function<void(const DbResult&)> callback)
{
	QMetaObject::invokeMethod(this, [this, table, condition, callback]() {
		QUuid callbackId = QUuid::createUuid();
		if (callback) {
			m_callbacks[callbackId] = callback;
		}
		QVariantMap params;
		params["table"] = table;
		params["condition"] = condition;
		onNonTransOp("delete", params, callbackId);
		}, Qt::QueuedConnection);
}

// 非事务操作统一处理
void DbMgr::onNonTransOp(const QString& opType, const QVariantMap& params, const QUuid& callbackId) {
	qDebug() << "[DbMgr线程] 收到操作:" << opType << "CallbackId:" << callbackId.toString();

	if (!checkConnection()) {
		qDebug() << "[DbMgr线程] 数据库连接检查失败";
		emit sigResult(callbackId, DbResult::fail(m_lastError));
		return;
	}

	if (opType == "select") {
		QString table = params["table"].toString();
		QString fields = params["fields"].toString();
		QString condition = params["condition"].toString();
		QString join = params["join"].toString();

		QString sql = QString("SELECT %1 FROM %2").arg(fields).arg(table);
		if (!join.isEmpty()) sql += " " + join;
		if (!condition.isEmpty()) sql += " WHERE " + condition;

		QSqlQuery query(m_database);
		if (!query.exec(sql)) {
			QString error = query.lastError().text() + " (SQL: " + sql + ")";
			emit sigResult(callbackId, DbResult::fail(error));
			return;
		}

		QList<QVariantMap> data;
		while (query.next()) {
			QVariantMap row;
			QSqlRecord record = query.record();
			for (int i = 0; i < record.count(); ++i) {
				row[record.fieldName(i)] = query.value(i);
			}
			data.append(row);
		}
		emit sigResult(callbackId, DbResult::success(data));
	}
	else if (opType == "insert") {
		QString table = params["table"].toString();
		QVariantMap values = params["values"].toMap();

		if (values.isEmpty()) {
			emit sigResult(callbackId, DbResult::fail("插入数据不能为空"));
			return;
		}

		QStringList fields, placeholders;
		QVariantList bindValues;
		for (auto it = values.begin(); it != values.end(); ++it) {
			fields.append(it.key());
			placeholders.append("?");
			bindValues.append(it.value());
		}

		QString sql = QString("INSERT INTO %1 (%2) VALUES (%3)")
			.arg(table).arg(fields.join(",")).arg(placeholders.join(","));

		QSqlQuery query(m_database);
		query.prepare(sql);
		for (int i = 0; i < bindValues.size(); ++i) {
			query.bindValue(i, bindValues[i]);
		}

		if (query.exec()) {
			emit sigResult(callbackId, DbResult::success());
		}
		else {
			QString error = query.lastError().text() + " (SQL: " + sql + ")";
			emit sigResult(callbackId, DbResult::fail(error));
		}
	}
	else if (opType == "update") {
		QString table = params["table"].toString();
		QVariantMap values = params["values"].toMap();
		QString condition = params["condition"].toString();

		if (values.isEmpty()) {
			emit sigResult(callbackId, DbResult::fail("更新数据不能为空"));
			return;
		}
		if (condition.isEmpty()) {
			emit sigResult(callbackId, DbResult::fail("更新条件不能为空"));
			return;
		}

		QStringList updateFields;
		QVariantList bindValues;
		for (auto it = values.begin(); it != values.end(); ++it) {
			updateFields.append(QString("%1 = ?").arg(it.key()));
			bindValues.append(it.value());
		}

		QString sql = QString("UPDATE %1 SET %2 WHERE %3")
			.arg(table).arg(updateFields.join(",")).arg(condition);

		QSqlQuery query(m_database);
		query.prepare(sql);
		for (int i = 0; i < bindValues.size(); ++i) {
			query.bindValue(i, bindValues[i]);
		}

		if (query.exec()) {
			emit sigResult(callbackId, DbResult::success());
		}
		else {
			QString error = query.lastError().text() + " (SQL: " + sql + ")";
			emit sigResult(callbackId, DbResult::fail(error));
		}
	}
	else if (opType == "delete") {
		QString table = params["table"].toString();
		QString condition = params["condition"].toString();

		if (condition.isEmpty()) {
			emit sigResult(callbackId, DbResult::fail("删除条件不能为空"));
			return;
		}

		QString sql = QString("DELETE FROM %1 WHERE %2").arg(table).arg(condition);
		QSqlQuery query(m_database);

		if (query.exec(sql)) {
			emit sigResult(callbackId, DbResult::success());
		}
		else {
			QString error = query.lastError().text() + " (SQL: " + sql + ")";
			emit sigResult(callbackId, DbResult::fail(error));
		}
	}
	else if (opType == "executeSql") {
		QString sql = params["sql"].toString();
		qDebug() << "[DbMgr线程] 执行自定义SQL:" << sql;
		if (sql.isEmpty()) {
			emit sigResult(callbackId, DbResult::fail("SQL语句不能为空"));
			return;
		}

		QSqlQuery query(m_database);
		if (query.exec(sql)) {
			qDebug() << "[DbMgr线程] SQL执行成功";
			// 若为查询语句，返回结果集；否则返回空成功
			if (query.isSelect()) {
				QList<QVariantMap> data;
				while (query.next()) {
					QVariantMap row;
					QSqlRecord record = query.record();
					for (int i = 0; i < record.count(); ++i) {
						row[record.fieldName(i)] = query.value(i);
					}
					data.append(row);
				}
				emit sigResult(callbackId, DbResult::success(data));
			}
			else {
				emit sigResult(callbackId, DbResult::success());
			}
		}
		else {
			QString error = query.lastError().text() + " (SQL: " + sql + ")";
			qDebug() << "[DbMgr线程] SQL执行失败:" << error;
			emit sigResult(callbackId, DbResult::fail(error));
		}
	}
}

// -------------------------- 事务操作实现 --------------------------
void DbMgr::beginTransaction(std::function<void(bool, const QString&, const QUuid&)> callback) {
	QMetaObject::invokeMethod(this, [this, callback]() {
		QUuid callbackId = QUuid::createUuid();
		QUuid transId = QUuid::createUuid();

		if (callback) {
			m_beginTransCallbacks[callbackId] = callback;
		}

		onBeginTrans(transId, callbackId);
		}, Qt::QueuedConnection);
}

void DbMgr::onBeginTrans(const QUuid& transId, const QUuid& callbackId) {
	bool success = false;
	QString error;

	if (!checkConnection()) {
		error = m_lastError;
		emit sigTransBeginResult(callbackId, success, error, transId);
		return;
	}

	success = m_database.transaction();
	if (success) {
		m_transactions[transId] = true;
	}
	else {
		error = m_database.lastError().text();
	}

	emit sigTransBeginResult(callbackId, success, error, transId);
}

void DbMgr::transSelect(const QUuid& transId, const QString& table, const QString& fields, const QString& condition, const QString& join, std::function<void(const DbResult&)> callback) {
	QMetaObject::invokeMethod(this, [this, transId, table, fields, condition, join, callback]() {
		QUuid callbackId = QUuid::createUuid();
		if (callback) {
			m_callbacks[callbackId] = callback;
		}

		QVariantMap params;
		params["table"] = table;
		params["fields"] = fields;
		params["condition"] = condition;
		params["join"] = join;
		onTransOp(transId, "select", params, callbackId);
		}, Qt::QueuedConnection);
}

void DbMgr::transInsert(const QUuid& transId, const QString& table, const QVariantMap& values, std::function<void(const DbResult&)> callback) {
	QUuid callbackId = QUuid::createUuid();
	if (callback) {
		m_callbacks[callbackId] = callback;
	}

	QVariantMap params;
	params["table"] = table;
	params["values"] = values;
	emit sigTransOp(transId, "insert", params, callbackId);
}

void DbMgr::transUpdate(const QUuid& transId, const QString& table, const QVariantMap& values, const QString& condition, std::function<void(const DbResult&)> callback) {
	QMetaObject::invokeMethod(this, [this, transId, table, values, condition, callback]() {
		QUuid callbackId = QUuid::createUuid();
		if (callback) {
			m_callbacks[callbackId] = callback;
		}

		QVariantMap params;
		params["table"] = table;
		params["values"] = values;
		params["condition"] = condition;
		onTransOp(transId, "update", params, callbackId);
		}, Qt::QueuedConnection);
}

void DbMgr::transDelete(const QUuid& transId, const QString& table, const QString& condition, std::function<void(const DbResult&)> callback)
{
	QMetaObject::invokeMethod(this, [this, transId, table, condition, callback]() {
		QUuid callbackId = QUuid::createUuid();
		if (callback) {
			m_callbacks[callbackId] = callback;
		}

		QVariantMap params;
		params["table"] = table;
		params["condition"] = condition;
		onTransOp(transId, "delete", params, callbackId);
		}, Qt::QueuedConnection);
}

void DbMgr::transExecuteSql(const QUuid& transId, const QString& sql, std::function<void(const DbResult&)> callback)
{
	QMetaObject::invokeMethod(this, [this, transId, sql, callback]() {
		QUuid callbackId = QUuid::createUuid();
		if (callback) {
			m_callbacks[callbackId] = callback;
		}

		QVariantMap params;
		params["sql"] = sql;
		onTransOp(transId, "executeSql", params, callbackId);
		}, Qt::QueuedConnection);
}

void DbMgr::onTransOp(const QUuid& transId, const QString& opType, const QVariantMap& params, const QUuid& callbackId) {
	if (!isTransactionActive(transId)) {
		emit sigResult(callbackId, DbResult::fail("事务不存在或已结束"));
		return;
	}
	if (!checkConnection()) {
		emit sigResult(callbackId, DbResult::fail(m_lastError));
		return;
	}

	// 复用非事务的SQL构建逻辑（仅增加事务上下文检查）
	if (opType == "select") {
		QString table = params["table"].toString();
		QString fields = params["fields"].toString();
		QString condition = params["condition"].toString();
		QString join = params["join"].toString();

		QString sql = QString("SELECT %1 FROM %2").arg(fields).arg(table);
		if (!join.isEmpty()) sql += " " + join;
		if (!condition.isEmpty()) sql += " WHERE " + condition;

		QSqlQuery query(m_database);
		if (!query.exec(sql)) {
			QString error = query.lastError().text() + " (SQL: " + sql + ")";
			emit sigResult(callbackId, DbResult::fail(error));
			return;
		}

		QList<QVariantMap> data;
		while (query.next()) {
			QVariantMap row;
			QSqlRecord record = query.record();
			for (int i = 0; i < record.count(); ++i) {
				row[record.fieldName(i)] = query.value(i);
			}
			data.append(row);
		}
		emit sigResult(callbackId, DbResult::success(data));
	}
	else if (opType == "insert") {
		QString table = params["table"].toString();
		QVariantMap values = params["values"].toMap();

		if (values.isEmpty()) {
			emit sigResult(callbackId, DbResult::fail("插入数据不能为空"));
			return;
		}

		QStringList fields, placeholders;
		QVariantList bindValues;
		for (auto it = values.begin(); it != values.end(); ++it) {
			fields.append(it.key());
			placeholders.append("?");
			bindValues.append(it.value());
		}

		QString sql = QString("INSERT INTO %1 (%2) VALUES (%3)")
			.arg(table).arg(fields.join(",")).arg(placeholders.join(","));

		QSqlQuery query(m_database);
		query.prepare(sql);
		for (int i = 0; i < bindValues.size(); ++i) {
			query.bindValue(i, bindValues[i]);
		}

		if (query.exec()) {
			emit sigResult(callbackId, DbResult::success());
		}
		else {
			QString error = query.lastError().text() + " (SQL: " + sql + ")";
			emit sigResult(callbackId, DbResult::fail(error));
		}
	}
	else if (opType == "update") {
		QString table = params["table"].toString();
		QVariantMap values = params["values"].toMap();
		QString condition = params["condition"].toString();

		if (values.isEmpty()) {
			emit sigResult(callbackId, DbResult::fail("更新数据不能为空"));
			return;
		}
		if (condition.isEmpty()) {
			emit sigResult(callbackId, DbResult::fail("更新条件不能为空"));
			return;
		}

		QStringList updateFields;
		QVariantList bindValues;
		for (auto it = values.begin(); it != values.end(); ++it) {
			updateFields.append(QString("%1 = ?").arg(it.key()));
			bindValues.append(it.value());
		}

		QString sql = QString("UPDATE %1 SET %2 WHERE %3")
			.arg(table).arg(updateFields.join(",")).arg(condition);

		QSqlQuery query(m_database);
		query.prepare(sql);
		for (int i = 0; i < bindValues.size(); ++i) {
			query.bindValue(i, bindValues[i]);
		}

		if (query.exec()) {
			emit sigResult(callbackId, DbResult::success());
		}
		else {
			QString error = query.lastError().text() + " (SQL: " + sql + ")";
			emit sigResult(callbackId, DbResult::fail(error));
		}
	}
	else if (opType == "delete") {
		QString table = params["table"].toString();
		QString condition = params["condition"].toString();

		if (condition.isEmpty()) {
			emit sigResult(callbackId, DbResult::fail("删除条件不能为空"));
			return;
		}

		QString sql = QString("DELETE FROM %1 WHERE %2").arg(table).arg(condition);
		QSqlQuery query(m_database);

		if (query.exec(sql)) {
			emit sigResult(callbackId, DbResult::success());
		}
		else {
			QString error = query.lastError().text() + " (SQL: " + sql + ")";
			emit sigResult(callbackId, DbResult::fail(error));
		}
	}
	else if (opType == "executeSql") {
		QString sql = params["sql"].toString();
		if (sql.isEmpty()) {
			emit sigResult(callbackId, DbResult::fail("SQL语句不能为空"));
			return;
		}

		QSqlQuery query(m_database);
		if (query.exec(sql)) {
			if (query.isSelect()) {
				QList<QVariantMap> data;
				while (query.next()) {
					QVariantMap row;
					QSqlRecord record = query.record();
					for (int i = 0; i < record.count(); ++i) {
						row[record.fieldName(i)] = query.value(i);
					}
					data.append(row);
				}
				emit sigResult(callbackId, DbResult::success(data));
			}
			else {
				emit sigResult(callbackId, DbResult::success());
			}
		}
		else {
			QString error = query.lastError().text() + " (SQL: " + sql + ")";
			emit sigResult(callbackId, DbResult::fail(error));
		}
	}
}

void DbMgr::commitTransaction(const QUuid& transId, std::function<void(bool, const QString&)> callback) {
	QMetaObject::invokeMethod(this, [this, transId, callback]() {
		QUuid callbackId = QUuid::createUuid();
		if (callback) {
			m_commitRollbackCallbacks[callbackId] = callback;
		}

		onCommitTrans(transId, callbackId);
		}, Qt::QueuedConnection);
}

void DbMgr::onCommitTrans(const QUuid& transId, const QUuid& callbackId) {
	bool success = false;
	QString error;

	if (!isTransactionActive(transId)) {
		error = "事务不存在或已结束";
		emit sigTransCommitRollbackResult(callbackId, success, error);
		return;
	}

	success = m_database.commit();
	if (!success) {
		error = m_database.lastError().text();
	}
	m_transactions.remove(transId);

	emit sigTransCommitRollbackResult(callbackId, success, error);
}

void DbMgr::rollbackTransaction(const QUuid& transId, std::function<void(bool, const QString&)> callback) {
	QMetaObject::invokeMethod(this, [this, transId, callback]() {
		QUuid callbackId = QUuid::createUuid();
		if (callback) {
			m_commitRollbackCallbacks[callbackId] = callback;
		}

		onRollbackTrans(transId, callbackId);
		}, Qt::QueuedConnection);
}

void DbMgr::onRollbackTrans(const QUuid& transId, const QUuid& callbackId) {
	bool success = false;
	QString error;

	if (!isTransactionActive(transId)) {
		error = "事务不存在或已结束";
		emit sigTransCommitRollbackResult(callbackId, success, error);
		return;
	}

	success = m_database.rollback();
	if (!success) {
		error = m_database.lastError().text();
	}
	m_transactions.remove(transId);

	emit sigTransCommitRollbackResult(callbackId, success, error);
}